/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pracamagisterska;

import com.mysql.jdbc.Blob;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.awt.Component;
import java.awt.image.BufferedImage;
import java.beans.PropertyVetoException;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.imageio.ImageIO;
import javax.swing.DefaultListModel;
import org.joda.time.DateTime;
import org.joda.time.Days;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

/**
 *
 * @author Adrian
 */
public class DatabaseThread extends Thread implements Runnable {

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private String DB_URL = "jdbc:mysql://localhost/";
    private String DB_NAME = "database";
    private String CHAR_ENC = "?characterEncoding=utf8";
    private String USER = "root";
    private String PASS = "";
    private Connection conn = null;
    private Statement stmt = null;
    private Start start = null;
    private ResultSet rs0 = null;
    private ResultSet rs = null;
    private boolean status = true;
    private boolean config_status = false;
    public MainWindow main = null;
    public Configuration config = null;
    private Login login_window = null;
    private ArrayList<Integer> listid = new ArrayList<>();
    private ArrayList personlist = null;
    public String hotelname;
    public BufferedImage hotellogo = null;

    public DatabaseThread(Start win) {
        start = win;
    }

    public DatabaseThread(Start win, String DB_URL, String DB_NAME, String User, String Pass) {
        start = win;
        setDB_URL(DB_URL);
        setDB_NAME(DB_NAME);
        setUSER(User);
        setPASS(Pass);
    }

    public void setDB_URL(String DB_URL) {
        this.DB_URL = DB_URL;
    }

    public void setDB_NAME(String DB_NAME) {
        this.DB_NAME = DB_NAME;
    }

    public void setUSER(String USER) {
        this.USER = USER;
    }

    public void setPASS(String PASS) {
        this.PASS = PASS;
    }

    public String getDB_URL() {
        return DB_URL;
    }

    public String getDB_NAME() {
        return DB_NAME;
    }

    public String getPASS() {
        return PASS;
    }

    public String getUSER() {
        return USER;
    }

    @Override
    public void run() {
        while (!status) {
            try {
                rs0 = stmt.executeQuery("SELECT status FROM pcconfig");
                while (rs0.next()) {
                    System.out.println("Database - connected");
                }
                rs0.close();
                this.sleep(10000);
            } catch (SQLException ex) {
                ModalityInternalFrame er = new Error(main.jDesktopPane2, "Połączenie z serwerem zostałem przerwane!");
                main.jDesktopPane2.add(er);
                status = true;
                reconnect();
            } catch (InterruptedException ex) {
                main.jDesktopPane2.add(new Error("Błąd wątku !"));
                status = true;
            }
        }
    }

    public void window() {
        System.out.println("Connecting to database...");
        try {
            conn = (Connection) DriverManager.getConnection(DB_URL + DB_NAME + CHAR_ENC, USER, PASS);
            stmt = (Statement) conn.createStatement();
            rs0 = stmt.executeQuery("SELECT * FROM pcconfig");
            rs0.next();
            config_status = rs0.getBoolean("status");
            if (config_status) {
                rs0.close();
                start.dispose();
                main = new MainWindow(this);
                main.setVisible(true);
                MainWindow_blockAll();
                window2();
                status = false;
                this.start();

            } else {
                rs0.close();
                start.dispose();
                main = new MainWindow(this);
                main.setVisible(true);
                MainWindow_blockAll();
                main.jDesktopPane2.add(config = new Configuration(main.jDesktopPane2, this, configuration_ComboBoxRefill()));
                config.show();
                status = false;
                this.start();

            }
            rs0.close();

        } catch (SQLException ex) {
            Logger.getLogger(Configuration.class.getName()).log(Level.SEVERE, null, ex);
            start.error("Nie można połączyć z bazą");
        }

    }

    public void MainWindow_blockAll() {
        Component[] com = main.jDesktopPane2.getComponents();
        for (int a = 0; a < com.length; a++) {
            com[a].setEnabled(false);
            com[a].setVisible(false);

        }
    }

    public void MainWindow_unblockAll() {
        Component[] com = main.jDesktopPane2.getComponents();
        for (int a = 0; a < com.length; a++) {
            com[a].setEnabled(true);
            com[a].setVisible(true);

        }
    }

    public void window2() throws SQLException {
        login_window = new Login(main);
        main.jDesktopPane2.add(login_window);
        main.jDesktopPane2.selectFrame(true).setLocation(main.jDesktopPane2.getWidth() / 2 - login_window.getWidth() / 2, 100);
        login_window.setVisible(true);
    }

    private void reconnect() {
        main.dispose();
        Start start = new Start();
        start.setVisible(true);
        this.interrupt();
    }

    protected String[] configuration_ComboBoxRefill() throws SQLException {
        String[] languages;
        String language;
        rs0 = stmt.executeQuery("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pcconfig' AND COLUMN_NAME = 'language' ");
        rs0.next();
        language = rs0.getString(1);
        rs0.close();
        language = language.substring(6);
        language = language.replace(')', ' ');
        language = language.replace('\'', ' ');
        language = language.replace(" ", "");
        languages = language.split(",");
        return languages;
    }

    protected boolean configuration_new(String language, String name, String user, String pass, String mail, byte[] fin) throws SQLException {
        if (!config_status) {
            if (fin != null) {
                PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET status = 1, hotellogo = ?,language=?,hotelname=?,password=?,user=?, mail=?, welcome=1");
                ps.setBinaryStream(1, new ByteArrayInputStream(fin), fin.length);
                ps.setString(2, language);
                ps.setString(3, name);
                ps.setString(4, pass);
                ps.setString(5, user);
                ps.setString(6, mail);
                ps.executeUpdate();
                return true;
            } else {
                PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET status = 1, language=?,hotelname=?,password=?,user=?, mail=?, welcome=1");
                ps.setString(1, language);
                ps.setString(2, name);
                ps.setString(3, pass);
                ps.setString(4, user);
                ps.setString(5, mail);
                ps.executeUpdate();
                return true;
            }
        }
        return false;
    }

    protected boolean configuration_change(String language, String name, String user, String pass, String mail, int status, byte[] fin) throws SQLException, IOException {
        if (pass != null && fin == null) {
            stmt.executeUpdate("UPDATE pcconfig SET status = 1, language ='" + language + "', hotelname='" + name + "', user='" + user + "',password='" + pass + "',mail='" + mail + "',welcome=" + status);
        } else if (pass == null && fin != null) {
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET status = 1, hotellogo = ?,language=?,hotelname=?,user=?, mail=?, welcome=?");
            ps.setBinaryStream(1, new ByteArrayInputStream(fin), fin.length);
            ps.setString(2, language);
            ps.setString(3, name);
            ps.setString(4, user);
            ps.setString(5, mail);
            ps.setInt(6, status);
            ps.executeUpdate();
        } else if (pass == null && fin == null) {
            stmt.executeUpdate("UPDATE pcconfig SET status = 1, language ='" + language + "', hotelname='" + name + "', user='" + user + "',mail='" + mail + "',welcome='" + status + "'");
        } else {
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET status = 1, hotellogo = ?,language=?,hotelname=?,user=?, mail=?, welcome=?,pass=?");
            ps.setBinaryStream(1, new ByteArrayInputStream(fin), fin.length);
            ps.setString(2, language);
            ps.setString(3, name);
            ps.setString(4, user);
            ps.setString(5, mail);
            ps.setInt(6, status);
            ps.setString(7, pass);
            ps.executeUpdate();
        }
        getHotelLogo();
        return true;
    }

    protected ArrayList<String> configuration_refill() throws SQLException {
        ArrayList<String> settings = new ArrayList<>();
        rs0 = stmt.executeQuery("SELECT * from pcconfig");
        rs0.next();
        settings.add(rs0.getString("language"));
        settings.add(rs0.getString("hotelname"));
        settings.add(rs0.getString("user"));
        settings.add(rs0.getString("mail"));
        settings.add(rs0.getString("welcome"));
        return settings;
    }

    protected void Login_check(String l, String p) throws SQLException, NoSuchAlgorithmException, IOException {
        String login = "";
        String pass = "";
        Boolean welcome = true;
        rs0 = stmt.executeQuery("SELECT * FROM pcconfig ");
        rs0.next();
        login = rs0.getString("user");
        pass = rs0.getString("password");
        welcome = rs0.getBoolean("welcome");
        hotelname = rs0.getString("hotelname");
        Blob imageBlob = (Blob) rs0.getBlob("hotellogo");
        getHotelLogo();
        if (login.equals(l)) {
            if (pass.equals(MD5(p))) {
                login_window.dispose();
                MainWindow_unblockAll();
                if (welcome) {
                    try {
                        Welcome w = new Welcome(main.jDesktopPane2, this);
                        main.jDesktopPane2.add(w);
                        w.setSelected(true);
                        w.show();
                    } catch (PropertyVetoException ex) {
                        Logger.getLogger(DatabaseThread.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            } else {
                login_window.jLabel5.setVisible(true);
            }
        } else {
            login_window.jLabel5.setVisible(true);
        }
    }

    protected boolean PasswordRecover_password(String user, String mail) {
        try {
            rs0 = stmt.executeQuery("SELECT user,mail FROM pcconfig");
            rs0.next();
            String username = rs0.getString("user");
            String email = rs0.getString("mail");
            if (username.equals(user) && email.equals(mail)) {
                return true;
            }
            return false;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    protected boolean PasswordRecover_login(String pass, String mail) {
        try {
            rs0 = stmt.executeQuery("SELECT password,mail FROM pcconfig");
            rs0.next();
            String password = rs0.getString("password");
            String email = rs0.getString("mail");
            if (password.equals(MD5(pass)) && email.equals(mail)) {
                return true;
            }
            return false;
        } catch (NoSuchAlgorithmException | SQLException ex) {
            Logger.getLogger(DatabaseThread.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    protected boolean PasswordChange(String password) {
        try {
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET password=?");
            ps.setString(1, MD5(password));
            ps.executeUpdate();
            return true;
        } catch (NoSuchAlgorithmException | SQLException ex) {
            Logger.getLogger(DatabaseThread.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    protected boolean LoginChange(String login) {
        try {
            PreparedStatement ps = (PreparedStatement) conn.prepareStatement("UPDATE pcconfig SET user=?");
            ps.setString(1, login);
            ps.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    protected void getHotelLogo() throws SQLException, IOException {
        rs = stmt.executeQuery("SELECT * FROM pcconfig ");
        rs.next();
        Blob imageBlob = (Blob) rs.getBlob("hotellogo");
        InputStream binaryStream = rs.getBinaryStream("hotellogo");
        hotellogo = ImageIO.read(binaryStream);
        rs.close();
    }

    protected DefaultListModel Review_ListRefill(String date) throws SQLException {
        DefaultListModel listModel = new DefaultListModel();
        listid = new ArrayList<>();
        rs0 = stmt.executeQuery("SELECT id,number FROM registration WHERE YEAR( arrival ) =  '" + date + "'");
        while (rs0.next()) {
            listModel.addElement(rs0.getInt("number") + "/" + date);
            listid.add(rs0.getInt("id"));
        }
        rs0.close();
        return listModel;
    }

    protected ArrayList<ReviewList> Review_PersonListRefill(int index) throws SQLException {
        personlist = new ArrayList();
        ArrayList<ReviewList> personListDetail = new ArrayList<>();
        ReviewList object = null;
        if (index != -1) {
            rs0 = stmt.executeQuery("SELECT * FROM registration WHERE id=" + listid.get(index));
            rs0.next();
            if (rs0.getInt("id_person1") != 0) {
                personlist.add(rs0.getInt("id_person1"));
            }
            if (rs0.getInt("id_person2") != 0) {
                personlist.add(rs0.getInt("id_person2"));
            }
            if (rs0.getInt("id_person3") != 0) {
                personlist.add(rs0.getInt("id_person3"));
            }
            if (rs0.getInt("id_person4") != 0) {
                personlist.add(rs0.getInt("id_person4"));
            }
            if (rs0.getInt("id_person5") != 0) {
                personlist.add(rs0.getInt("id_person5"));
            }
            if (rs0.getInt("id_person6") != 0) {
                personlist.add(rs0.getInt("id_person6"));
            }
            object = new ReviewList(rs0.getString("arrival"), rs0.getString("departure"), rs0.getInt("room"));
            personListDetail.add(object);
            if (personlist != null) {
                for (int i = 0; i < personlist.size(); i++) {
                    rs0 = stmt.executeQuery("SELECT * FROM person WHERE id=" + personlist.get(i));
                    rs0.next();
                    object = new ReviewList(rs0.getInt("id"), rs0.getString("name"), rs0.getString("surname"),
                            rs0.getString("mail"), rs0.getString("sex"), rs0.getString("birthplace"), rs0.getString("birthdate"), rs0.getString("nationality"), rs0.getString("address"), rs0.getString("identification"), rs0.getBoolean("newsletter"));
                    personListDetail.add(object);
                    rs0.close();
                }
            }

        }
        return personListDetail;

    }

    protected ArrayList<CardList> PersonStaticict_cardRefill() {
        try {
            ArrayList<CardList> cl = new ArrayList<>();
            rs0 = stmt.executeQuery("SELECT * from registration");
            while (rs0.next()) {
                if (rs0.getInt("id_person1") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person1")));
                }
                if (rs0.getInt("id_person2") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person2")));
                }
                if (rs0.getInt("id_person3") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person3")));
                }
                if (rs0.getInt("id_person4") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person4")));
                }
                if (rs0.getInt("id_person5") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person5")));
                }
                if (rs0.getInt("id_person6") != 0) {
                    cl.add(new CardList(rs0.getInt("id"), rs0.getInt("number"), rs0.getString("arrival"), rs0.getString("departure"), rs0.getString("room"), rs0.getInt("id_person6")));
                }
            }
            rs0.close();
            return cl;


        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        }
    }

    protected boolean PersonDetails_update(ReviewList item, int index, boolean type) {
        try {
            int news = item.isNews() ? 1 : 0;
            int id = 0;
            if (item.getId() != 0) {
                if (type) { // dodaj z listy i aktualizuj
                    stmt.executeUpdate("UPDATE person SET name='" + item.getName() + "', surname='" + item.getSurname() + "', sex='" + item.getSex() + "', birthdate='" + item.getBirthdate() + "', birthplace='" + item.getBirthplace() + "', nationality='" + item.getNationality()
                            + "', address='" + item.getAddress() + "', identification='" + item.getIdentity() + "', mail='" + item.getMail() + "', newsletter='" + news + "' WHERE id='" + item.getId() + "';");
                    if (personlist != null) {
                        stmt.executeUpdate("UPDATE registration SET id_person" + (personlist.size() + 1) + "='" + item.getId() + "' WHERE id='" + listid.get(index) + "';");
                    } else {
                        stmt.executeUpdate("UPDATE registration SET id_person1='" + item.getId() + "' WHERE id='" + listid.get(index) + "';");
                    }
                } else { //tylko aktualizuj
                    stmt.executeUpdate("UPDATE person SET name='" + item.getName() + "', surname='" + item.getSurname() + "', sex='" + item.getSex() + "', birthdate='" + item.getBirthdate() + "', birthplace='" + item.getBirthplace() + "', nationality='" + item.getNationality()
                            + "', address='" + item.getAddress() + "', identification='" + item.getIdentity() + "', mail='" + item.getMail() + "', newsletter='" + news + "' WHERE id='" + item.getId() + "';");
                }
            } else { //dodaj nowego klienta
                stmt.executeUpdate("INSERT INTO person SET name='" + item.getName() + "', surname='" + item.getSurname() + "', sex='" + item.getSex() + "', birthdate='" + item.getBirthdate() + "', birthplace='" + item.getBirthplace() + "', nationality='" + item.getNationality()
                        + "', address='" + item.getAddress() + "', identification='" + item.getIdentity() + "', mail='" + item.getMail() + "', newsletter='" + news + "';");
                rs0 = stmt.executeQuery("SELECT id from person where name='" + item.getName() + "' AND surname='" + item.getSurname() + "' AND birthplace='" + item.getBirthplace() + "';");
                rs0.next();
                id = rs0.getInt("id");
                if (personlist != null) {
                    System.out.println("os1");
                    stmt.executeUpdate("UPDATE registration SET id_person" + (personlist.size() + 1) + "='" + id + "' WHERE id='" + listid.get(index) + "';");
                } else {
                    System.out.println("brak osob na liscie");
                    stmt.executeUpdate("UPDATE registration SET id_person1='" + id + "' WHERE id='" + listid.get(index) + "';");
                }
            }
            return true;


        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return false;
        }
    }

    protected void Review_deletePerson(int row, int index) throws SQLException {
        stmt.executeUpdate("UPDATE  registration SET `id_person" + row + "`= NULL WHERE id =" + listid.get(index) + ";");
        personlist.remove(row - 1);
        int i;
        for (i = 1; i <= personlist.size(); i++) {
            stmt.executeUpdate("UPDATE registration SET id_person" + i + "='" + personlist.get(i - 1) + "' WHERE id=" + listid.get(index) + ";");

        }
        stmt.executeUpdate("UPDATE registration SET id_person" + (i) + "=" + null + " WHERE id=" + listid.get(index) + ";");
    }

    protected void Card_change(ReviewList item, int index) {
        try {
            if (index != 0) {
                stmt.executeUpdate("UPDATE registration SET arrival='" + item.getCheckin() + "', departure='" + item.getCheckout() + "', room='" + item.getApt() + "' WHERE id='" + listid.get(index) + "';");
            } else {
                DateFormat sdf = new SimpleDateFormat("yyyy");
                try {
                    rs0 = stmt.executeQuery("SELECT count(*) AS wynik FROM registration WHERE YEAR(arrival)=" + sdf.format(sdf.parse(item.getCheckin())));
                    rs0.next();
                    int number = rs0.getInt("wynik");
                    number++;
                    stmt.executeUpdate("INSERT INTO registration SET arrival='" + item.getCheckin() + "', departure='" + item.getCheckout() + "', room=" + item.getApt() + ", number=" + number);
                   /* rs0 = stmt.executeQuery("SELECT id FROM registration WHERE YEAR(arrival)='" + sdf.format(sdf.parse(item.getCheckin())) +"' AND number="+number);
                    rs0.next();
                    Review_ListRefill(sdf.format(sdf.parse(item.getCheckin())));
                    Review_PersonListRefill(rs0.getInt("id"));*/

                } catch (ParseException ex) {
                    Logger.getLogger(DatabaseThread.class
                            .getName()).log(Level.SEVERE, null, ex);
                };
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
    }

    protected ArrayList<ReviewList> PersonReview_search(String name, String surname, String document, String nationality) {
        try {
            ArrayList<ReviewList> list = new ArrayList<>();
            rs0 = stmt.executeQuery("SELECT * from person WHERE name like '%" + name + "%' AND surname like '%" + surname + "%' AND identification like '%" + document + "%' AND nationality like '%" + nationality + "%'");
            while (rs0.next()) {
                list.add(new ReviewList(rs0.getInt("id"), rs0.getString("name"), rs0.getString("surname"),
                        rs0.getString("mail"), rs0.getString("sex"), rs0.getString("birthplace"), rs0.getString("birthdate"), rs0.getString("nationality"), rs0.getString("address"), rs0.getString("identification"), rs0.getBoolean("newsletter")));
            }
            return list;


        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        }
    }

    protected Map<String, Integer> Statistic_getGuestOrigin(String date1, String date2) {
        try {
            ArrayList<Integer> list_of_person = new ArrayList<>();
            ArrayList<String> list_of_nationality = new ArrayList<>();
            Map<String, Integer> freq = new HashMap<String, Integer>();
            rs0 = stmt.executeQuery("SELECT * from registration WHERE arrival >='" + date1 + "' AND departure<='" + date2 + "';");
            while (rs0.next()) {
                if (rs0.getInt("id_person1") != 0) {
                    list_of_person.add(rs0.getInt("id_person1"));
                }
                if (rs0.getInt("id_person2") != 0) {
                    list_of_person.add(rs0.getInt("id_person2"));
                }
                if (rs0.getInt("id_person3") != 0) {
                    list_of_person.add(rs0.getInt("id_person3"));
                }
                if (rs0.getInt("id_person4") != 0) {
                    list_of_person.add(rs0.getInt("id_person4"));
                }
                if (rs0.getInt("id_person5") != 0) {
                    list_of_person.add(rs0.getInt("id_person5"));
                }
                if (rs0.getInt("id_person6") != 0) {
                    list_of_person.add(rs0.getInt("id_person6"));
                }
            }

            for (int i = 0; i < list_of_person.size(); i++) {
                rs0 = stmt.executeQuery("SELECT nationality from person WHERE id=" + list_of_person.get(i));
                rs0.next();
                list_of_nationality.add(rs0.getString("nationality"));
            }
            for (int i = 0; i < list_of_nationality.size(); i++) {
                int count = freq.containsKey(list_of_nationality.get(i)) ? freq.get(list_of_nationality.get(i)) : 0;
                freq.put(list_of_nationality.get(i), count + 1);
            }
            return freq;


        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        }
    }

    protected Map<String, Integer> Statistic_getRentedRoom(String date1, String date2) {
        try {
            ArrayList<Integer> list_of_rooms = new ArrayList<>();
            ArrayList<String> list_of_dates = new ArrayList<>();
            Map<String, Integer> freq = new HashMap<String, Integer>();

            DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            DateTime data1 = new DateTime(sdf.parse(date1));
            DateTime data2 = new DateTime(sdf.parse(date2));
            DateTimeFormatter fmt = DateTimeFormat.forPattern("MM");
            DateTimeFormatter fmtd = DateTimeFormat.forPattern("dd");

            int days = Days.daysBetween(data1, data2).getDays();
            for (int i = 0; i <= days; i++) {
                list_of_dates.add(data1.getYear() + "-" + fmt.print(data1) + "-" + fmtd.print(data1));
                data1 = data1.plusDays(1);
            }
            for (int i = 0; i < list_of_dates.size(); i++) {
                rs0 = stmt.executeQuery("SELECT room from registration WHERE '" + list_of_dates.get(i) + "' between arrival and departure;");
                while (rs0.next()) {
                    list_of_rooms.add(rs0.getInt("room"));
                }
            }
            for (int i = 0; i < list_of_rooms.size(); i++) {
                int count = freq.containsKey(list_of_rooms.get(i).toString()) ? freq.get(list_of_rooms.get(i).toString()) : 0;
                freq.put(list_of_rooms.get(i).toString(), count + 1);
            }

            return freq;


        } catch (ParseException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        }
    }

    protected Map<String, Integer> Statistic_getGuestNumber(String date1, String date2) {
        try {
            //lista osob w kazdym dniu pomiedzy datami ^_^
            ArrayList<Integer> list_of_person = new ArrayList<>();
            ArrayList<String> list_of_dates = new ArrayList<>();
            Map<String, Integer> freq = new HashMap<String, Integer>();
            DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            DateTime data1 = new DateTime(sdf.parse(date1));
            DateTime data2 = new DateTime(sdf.parse(date2));
            DateTimeFormatter fmt = DateTimeFormat.forPattern("MM");
            DateTimeFormatter fmtd = DateTimeFormat.forPattern("dd");
            int days = Days.daysBetween(data1, data2).getDays();
            for (int i = 0; i <= days; i++) {
                list_of_dates.add(data1.getYear() + "-" + fmt.print(data1) + "-" + fmtd.print(data1));
                data1 = data1.plusDays(1);
            }
            for (int i = 0; i < list_of_dates.size(); i++) {
                rs0 = stmt.executeQuery("select * from registration where '" + list_of_dates.get(i) + "' between arrival and departure");
                list_of_person.add(0);
                while (rs0.next()) {
                    if (rs0.getInt("id_person1") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                    if (rs0.getInt("id_person2") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                    if (rs0.getInt("id_person3") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                    if (rs0.getInt("id_person4") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                    if (rs0.getInt("id_person5") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                    if (rs0.getInt("id_person6") != 0) {
                        list_of_person.set(i, (list_of_person.get(i) + 1));
                    }
                }
            }
            for (int i = 0; i < list_of_dates.size(); i++) {
                freq.put(list_of_dates.get(i), list_of_person.get(i));
            }
            return freq;


        } catch (ParseException | SQLException ex) {
            Logger.getLogger(DatabaseThread.class
                    .getName()).log(Level.SEVERE, null, ex);

            return null;
        }
    }

    protected void Welcome_noshow() throws SQLException {
        stmt.executeUpdate("UPDATE pcconfig SET welcome = 0");
    }

    protected void resetStatus() throws SQLException {
        stmt.executeUpdate("Update pcconfig SET status = 0");
    }

    protected String MD5(String str) throws NoSuchAlgorithmException {
        MessageDigest m = MessageDigest.getInstance("MD5");
        m.update(str.getBytes(), 0, str.length());
        String x = new BigInteger(1, m.digest()).toString(16);
        return x;
    }
}
